package twittercrawler.utils;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * The Database class is used to provide all of the low-level JDBC services 
 * @author Renaud Richardet
 */
public class JdbcDatabase {

	static final Logger LOG = LoggerFactory.getLogger(JdbcDatabase.class);

	protected Connection connection;

	/**
	 * @param host
	 * @param dbName
	 * @param user
	 * @param pwd
	 * @throws DatabaseException if it can't connect to this database
	 */
	public JdbcDatabase(String host, String dbName, String user, String pwd) throws SQLException {
		this.connection = getDBConn(host, dbName, user, pwd);
	}

	/**
	 * @param host String
	 * @param database String
	 * @param user String
	 * @param pwd String
	 * @return Connection
	 * @throws SQLException
	 */
	public static Connection getDBConn(String host, String database, String user, String pwd) throws SQLException {

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		}
		catch (Exception e) {
			throw new SQLException("could not load jdbc driver");
		}
		if (host == null) {
			host = "localhost";
		}
		user = (user.equals("NULL")) ? "" : user;
		pwd = (pwd.equals("NULL")) ? "" : pwd;

		// connect to db mysql first
		Connection conn = DriverManager.getConnection("jdbc:mysql://" + host + "/mysql", user, pwd);

		// create the db if it does not exist yet
		// String sql = "CREATE DATABASE IF NOT EXISTS " + database;
		// conn.createStatement().execute(sql);

		// connect to db
		String jdbc = "jdbc:mysql://" + host + "/" + database + "?autoReconnect=true";
		conn = DriverManager.getConnection(jdbc, user, pwd);
		return conn;
	}

	public static Connection getDBConn(String dbName) throws SQLException {
		return getDBConn(null, dbName, "root", "");
	}

	/**
	 * Called to close the database.
	 * @throws DatabaseException Thrown if the connection cannot be closed.
	 */
	public void close() throws SQLException {
		connection.close();
	}

	/**
	 * Generate the DROP statement for a table.
	 * @param table The name of the table to drop.
	 * @return The SQL to drop a table.
	 */
	public String generateDrop(String table) {
		StringBuffer result = new StringBuffer();
		result.append("DROP TABLE ");
		result.append(table);
		result.append(";\n");
		return result.toString();
	}

	/**
	 * Execute a SQL query and return a ResultSet.
	 * @param sql The SQL query to execute.
	 * @return The ResultSet generated by the query.
	 * @throws DatabaseException If a datbase error occurs.
	 */
	public ResultSet executeQuery(String sql) throws SQLException {
		return connection.createStatement().executeQuery(sql);
	}

	/**
	 * Execute a SQL query
	 * @param sql The SQL query to execute.
	 * @throws DatabaseException If a datbase error occurs.
	 */
	public void execute(String sql) throws SQLException {
		connection.createStatement().execute(sql);
	}

	/**
	 * Get a list of all tables in the database.
	 * @return A list of all tables in the database.
	 * @throws DatabaseException If a database error occurs.
	 */
	public Collection<String> listTables() throws SQLException {

		Collection<String> result = new ArrayList<String>();
		DatabaseMetaData dbm = connection.getMetaData();
		String types[] = { "TABLE" };
		ResultSet rs = dbm.getTables(null, null, "", types);
		while (rs.next()) {
			String str = rs.getString("TABLE_NAME");
			result.add(str);
		}
		return result;
	}

	/**
	 * Get a list of all of the columns on a table.
	 * @param table The table to check.
	 * @return A list of all of the columns.
	 * @throws DatabaseException If a database error occurs.
	 */
	public Collection<String> listColumns(String table) throws SQLException {
		Collection<String> result = new ArrayList<String>();
		DatabaseMetaData dbm = connection.getMetaData();
		ResultSet rs = dbm.getColumns(null, null, table, null);
		while (rs.next()) {
			result.add(rs.getString("COLUMN_NAME"));
		}
		return result;
	}

	/**
	 * Get a list of all of the columns w/ additional infos on a table.
	 * @param table The table to check.
	 * @return A list of all of the columns.
	 * @throws DatabaseException If a database error occurs.
	 */
	public Collection<Map<String, Object>> listColumnsDetails(String table) throws SQLException {
		Collection<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		ResultSet rs = null;
		DatabaseMetaData dbm = connection.getMetaData();
		rs = dbm.getColumns(null, null, table, null);
		while (rs.next()) {
			Map<String, Object> resMap = new HashMap<String, Object>();

			resMap.put("COLUMN_NAME", rs.getString("COLUMN_NAME"));
			resMap.put("DATA_TYPE", rs.getInt("DATA_TYPE"));
			resMap.put("COLUMN_DEF", rs.getString("COLUMN_DEF"));

			// System.out.println(rs.getString("COLUMN_NAME")+" type: "+rs.getInt("DATA_TYPE")+" with "+rs.getString("COLUMN_DEF"));

			result.add(resMap);
		}
		if (rs != null) {
			rs.close();
		}
		return result;
	}

	/**
	 * Create a prepared statement.
	 * @param sql The SQL of the prepared statement.
	 * @return The PreparedStatement that was created.
	 * @throws DatabaseException If a database error occurs.
	 */
	public PreparedStatement prepareStatement(String sql) throws SQLException {
		return connection.prepareStatement(sql);
	}

	public Statement getStatement() throws SQLException {
		return connection.createStatement();
	}

	public static boolean exists(String datasetName) {
		return false;
	}
}